﻿using IDAdmin.Lib.Utils;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Globalization;
using System.Linq;
using System.Text;

namespace IDAdmin.Lib.DataLayer
{
    public class LoginNewSaveWebDB
    {
        List<string> nauList = new List<string>();
        int nauNum;
        public int getNau(DateTime date)
        {
            nauList.Clear();
            using (SqlConnection connection = ConnectionHelper.GetConnection())
            {
                string commandText = "select DISTINCT(account) as account  from nau where datetime between '{0}' and '{1}'";
                commandText = string.Format(commandText, date.ToString("yyyy-MM-dd") + " 00:00:00", date.ToString("yyyy-MM-dd") + " 23:59:59");
                SqlCommand cmd = new SqlCommand(commandText, connection);
                cmd.CommandTimeout = 3600;
                SqlDataReader dataReader = cmd.ExecuteReader();
                while (dataReader.Read())
                {
                    nauList.Add(dataReader["account"].ToString().ToLower());
                }
                dataReader.Close();
            }
            nauNum = nauList.Count;
            return nauNum;
        }

        public void getDaySecondSave(DateTime date, out string daySecondSave, out string percentDaySecondSave)
        {
            //string secondDayTimeStamp = DataToolUtil.truncateTime(DataToolUtil.kindDateTimeToKindUnixTimestamp(secondDay, DateTimeKind.Local).ToString(), "dd/MM/yyyy");
            List<string> accountAtSenconDay = new List<string>();
            int num = 0;
            TimeSpan difference = DateTime.Now.Date - date.Date;
            int days = (int)difference.TotalDays;

            if (days >= 1)
            {
                using (SqlConnection conn = ConnectionHelper.GetConnection())
                {
                    DateTime secondDay = date.AddDays(1);
                    double fromTS = DataToolUtil.kindDateTimeToKindUnixTimestamp(DateTime.ParseExact(secondDay.ToString("yyyy-MM-dd") + " 00:00:00", "yyyy-MM-dd HH:mm:ss", CultureInfo.InvariantCulture), DateTimeKind.Local);
                    double toTS = DataToolUtil.kindDateTimeToKindUnixTimestamp(DateTime.ParseExact(secondDay.ToString("yyyy-MM-dd") + " 23:59:59", "yyyy-MM-dd HH:mm:ss", CultureInfo.InvariantCulture), DateTimeKind.Local);
                    string cmdText = "Select distinct(account) as account from au where timestamp >= {0} and timestamp <={1}";
                    cmdText = string.Format(cmdText, fromTS, toTS);
                    SqlCommand command = new SqlCommand(cmdText, conn);
                    command.CommandTimeout = 3600;
                    SqlDataReader dataReader = command.ExecuteReader();
                    while (dataReader.Read())
                    {
                        accountAtSenconDay.Add(dataReader.GetString(0).ToLower());
                    }
                    dataReader.Close();
                }
                foreach (var account in nauList)
                {
                    var count = (from tb in accountAtSenconDay.AsParallel() where tb.ToLower().Equals(account.ToLower()) select new { tb }).FirstOrDefault();
                    if (count != null)
                    {
                        num++;
                    }
                }
            }
            daySecondSave = num.ToString();
            percentDaySecondSave = "";
            if (nauNum != 0)
            {
                percentDaySecondSave = ((num * 100 / nauNum)) + "%";
            }

        }

        public void getDayThirdSave(DateTime date, out string dayThirdSave, out string percentDayThirdSave)
        {
            //string secondDayTimeStamp = DataToolUtil.truncateTime(DataToolUtil.kindDateTimeToKindUnixTimestamp(secondDay, DateTimeKind.Local).ToString(), "dd/MM/yyyy");
            List<string> accountAtThirdDay = new List<string>();
            int num = 0;

            TimeSpan difference = DateTime.Now.Date - date.Date;
            int days = (int)difference.TotalDays;
            if (days >= 2)
            {
                using (SqlConnection conn = ConnectionHelper.GetConnection())
                {
                    DateTime thirdDay = date.AddDays(2);
                    double fromTS = DataToolUtil.kindDateTimeToKindUnixTimestamp(DateTime.ParseExact(thirdDay.ToString("yyyy-MM-dd") + " 00:00:00", "yyyy-MM-dd HH:mm:ss", CultureInfo.InvariantCulture), DateTimeKind.Local);
                    double toTS = DataToolUtil.kindDateTimeToKindUnixTimestamp(DateTime.ParseExact(thirdDay.ToString("yyyy-MM-dd") + " 23:59:59", "yyyy-MM-dd HH:mm:ss", CultureInfo.InvariantCulture), DateTimeKind.Local);
                    string cmdText = "Select distinct(account) as account from au where timestamp >= {0} and timestamp <={1}";
                    cmdText = string.Format(cmdText, fromTS, toTS);
                    SqlCommand command = new SqlCommand(cmdText, conn);
                    command.CommandTimeout = 3600;
                    SqlDataReader dataReader = command.ExecuteReader();
                    while (dataReader.Read())
                    {
                        accountAtThirdDay.Add(dataReader.GetString(0).ToLower());
                    }
                    dataReader.Close();
                }
                foreach (var account in nauList)
                {
                    var count = (from tb in accountAtThirdDay.AsParallel() where tb.ToLower().Equals(account.ToLower()) select new { tb }).FirstOrDefault();
                    if (count != null)
                    {
                        num++;
                    }
                }
            }
            dayThirdSave = num.ToString();
            percentDayThirdSave = "";
            if (nauNum != 0)
            {
                percentDayThirdSave = ((num * 100 / nauNum)) + "%";
            }
        }

        public void getDaySevenSave(DateTime date, out string daySevenSave, out string percentDaySevenSave)
        {
            //string secondDayTimeStamp = DataToolUtil.truncateTime(DataToolUtil.kindDateTimeToKindUnixTimestamp(secondDay, DateTimeKind.Local).ToString(), "dd/MM/yyyy");
            List<string> accountAtSevenDay = new List<string>();
            int num = 0;

            DateTime secondDay = date.AddDays(1);
            TimeSpan difference = DateTime.Now.Date - date.Date;
            int days = (int)difference.TotalDays;
            if (days >= 7)
            {
                using (SqlConnection conn = ConnectionHelper.GetConnection())
                {
                    DateTime sevenDay = date.AddDays(7);
                    double fromTS = DataToolUtil.kindDateTimeToKindUnixTimestamp(DateTime.ParseExact(sevenDay.ToString("yyyy-MM-dd") + " 00:00:00", "yyyy-MM-dd HH:mm:ss", CultureInfo.InvariantCulture), DateTimeKind.Local);
                    double toTS = DataToolUtil.kindDateTimeToKindUnixTimestamp(DateTime.ParseExact(sevenDay.ToString("yyyy-MM-dd") + " 23:59:59", "yyyy-MM-dd HH:mm:ss", CultureInfo.InvariantCulture), DateTimeKind.Local);
                    string cmdText = "Select distinct(account) as account from au where timestamp >= {0} and timestamp <={1}";
                    cmdText = string.Format(cmdText, fromTS, toTS);
                    SqlCommand command = new SqlCommand(cmdText, conn);
                    command.CommandTimeout = 3600;
                    SqlDataReader dataReader = command.ExecuteReader();
                    while (dataReader.Read())
                    {
                        accountAtSevenDay.Add(dataReader.GetString(0).ToLower());
                    }
                    dataReader.Close();
                }
                foreach (var account in nauList)
                {
                    var count = (from tb in accountAtSevenDay.AsParallel() where tb.ToLower().Equals(account.ToLower()) select new { tb }).FirstOrDefault();
                    if (count != null)
                    {
                        num++;
                    }
                }
            }
            daySevenSave = num.ToString();
            percentDaySevenSave = "";
            if (nauNum != 0)
            {
                percentDaySevenSave = ((num * 100 / nauNum)) + "%";
            }
        }

        public void getDayFourTeenDaySave(DateTime date, out string dayfourTeenDaySave, out string percentDayfourTeenDaySave)
        {
            //string secondDayTimeStamp = DataToolUtil.truncateTime(DataToolUtil.kindDateTimeToKindUnixTimestamp(secondDay, DateTimeKind.Local).ToString(), "dd/MM/yyyy");
            List<string> accountAtFourTeenDay = new List<string>();
            int num = 0;

            DateTime secondDay = date.AddDays(1);
            TimeSpan difference = DateTime.Now.Date - date.Date;
            int days = (int)difference.TotalDays;
            if (days >= 14)
            {
                using (SqlConnection conn = ConnectionHelper.GetConnection())
                {
                    DateTime fourTeenDayDay = date.AddDays(14);
                    double fromTS = DataToolUtil.kindDateTimeToKindUnixTimestamp(DateTime.ParseExact(fourTeenDayDay.ToString("yyyy-MM-dd") + " 00:00:00", "yyyy-MM-dd HH:mm:ss", CultureInfo.InvariantCulture), DateTimeKind.Local);
                    double toTS = DataToolUtil.kindDateTimeToKindUnixTimestamp(DateTime.ParseExact(fourTeenDayDay.ToString("yyyy-MM-dd") + " 23:59:59", "yyyy-MM-dd HH:mm:ss", CultureInfo.InvariantCulture), DateTimeKind.Local);
                    string cmdText = "Select distinct(account) as account from au where timestamp >= {0} and timestamp <={1}";
                    cmdText = string.Format(cmdText, fromTS, toTS);
                    SqlCommand command = new SqlCommand(cmdText, conn);
                    command.CommandTimeout = 3600;
                    SqlDataReader dataReader = command.ExecuteReader();
                    while (dataReader.Read())
                    {
                        accountAtFourTeenDay.Add(dataReader.GetString(0).ToLower());
                    }
                    dataReader.Close();
                }
                foreach (var account in nauList)
                {
                    var count = (from tb in accountAtFourTeenDay.AsParallel() where tb.ToLower().Equals(account.ToLower()) select new { tb }).FirstOrDefault();
                    if (count != null)
                    {
                        num++;
                    }
                }
            }
            dayfourTeenDaySave = num.ToString();
            percentDayfourTeenDaySave = "";
            if (nauNum != 0)
            {
                percentDayfourTeenDaySave = ((num * 100 / nauNum)) + "%";
            }
        }

        public void getDayThirtyDaySave(DateTime date, out string dayThirtyDaySave, out string percentDayThirtyDaySave)
        {
            //string secondDayTimeStamp = DataToolUtil.truncateTime(DataToolUtil.kindDateTimeToKindUnixTimestamp(secondDay, DateTimeKind.Local).ToString(), "dd/MM/yyyy");
            List<string> accountAtThirtyDay = new List<string>();
            int num = 0;

            TimeSpan difference = DateTime.Now.Date - date.Date;
            int days = (int)difference.TotalDays;
            if (days >= 30)
            {
                using (SqlConnection conn = ConnectionHelper.GetConnection())
                {
                    DateTime thirtyDay = date.AddDays(30);
                    double fromTS = DataToolUtil.kindDateTimeToKindUnixTimestamp(DateTime.ParseExact(thirtyDay.ToString("yyyy-MM-dd") + " 00:00:00", "yyyy-MM-dd HH:mm:ss", CultureInfo.InvariantCulture), DateTimeKind.Local);
                    double toTS = DataToolUtil.kindDateTimeToKindUnixTimestamp(DateTime.ParseExact(thirtyDay.ToString("yyyy-MM-dd") + " 23:59:59", "yyyy-MM-dd HH:mm:ss", CultureInfo.InvariantCulture), DateTimeKind.Local);
                    string cmdText = "Select distinct(account) as account from au where timestamp >= {0} and timestamp <={1}";
                    cmdText = string.Format(cmdText, fromTS, toTS);
                    SqlCommand command = new SqlCommand(cmdText, conn);
                    command.CommandTimeout = 3600;
                    SqlDataReader dataReader = command.ExecuteReader();
                    while (dataReader.Read())
                    {
                        accountAtThirtyDay.Add(dataReader.GetString(0).ToLower());
                    }
                    dataReader.Close();
                }
                foreach (var account in nauList)
                {
                    var count = (from tb in accountAtThirtyDay.AsParallel() where tb.ToLower().Equals(account.ToLower()) select new { tb }).FirstOrDefault();
                    if (count != null)
                    {
                        num++;
                    }
                }
            }
            dayThirtyDaySave = num.ToString();
            percentDayThirtyDaySave = "";
            if (nauNum != 0)
            {
                percentDayThirtyDaySave = ((num * 100 / nauNum)) + "%";
            }
        }

        public void insertLoginNewSave(List<LoginNewSave> data, List<DateTime> dateTimeList)
        {
            using (SqlConnection conn = ConnectionHelper.GetConnection())
            {//sp_Insert_PremiumLogin

                SqlCommand deleteCommand = new SqlCommand("spTransfer_delete_LoginNewSave", conn);
                deleteCommand.CommandTimeout = 3600;
                deleteCommand.CommandType = CommandType.StoredProcedure;
                deleteCommand.Parameters.AddWithValue("@fromDT", dateTimeList[0].ToString("yyyy-MM-dd") + " 00:00:00");
                deleteCommand.Parameters.AddWithValue("@toDT", dateTimeList[dateTimeList.Count - 1].ToString("yyyy-MM-dd") + " 23:59:59");
                deleteCommand.ExecuteNonQuery();
            }
            foreach (LoginNewSave item in data)
            {
                try
                {
                    using (SqlConnection conn = ConnectionHelper.GetConnection())
                    {
                        SqlCommand command = new SqlCommand("sp_Insert_LoginNewSave", conn);
                        command.CommandTimeout = 3600;
                        command.CommandType = CommandType.StoredProcedure;
                        command.Parameters.AddWithValue("@datetime", item.DateTime);
                        command.Parameters.AddWithValue("@Nau", item.Nau);
                        command.Parameters.AddWithValue("@DaySecondSave", item.DaySecondSave);
                        command.Parameters.AddWithValue("@DayThirdSave", item.DayThirdSave);
                        command.Parameters.AddWithValue("@DaySevenSave", item.DaySevenSave);
                        command.Parameters.AddWithValue("@DayFourteenSave", item.DayFourteenSave);
                        command.Parameters.AddWithValue("@DayThirtySave", item.DayThirtySave);
                        command.Parameters.AddWithValue("@PercentDaySecondSave", item.PercentDaySecondSave);
                        command.Parameters.AddWithValue("@PercentDayThirdSave", item.PercentDayThirdSave);
                        command.Parameters.AddWithValue("@PercentDaySevenSave", item.PercentDaySevenSave);
                        command.Parameters.AddWithValue("@PercentDayFourteenSave", item.PercentDayFourteenSave);
                        command.Parameters.AddWithValue("@PercentDayThirtySave", item.PercentDayThirtySave);
                        command.ExecuteNonQuery();
                    }
                }catch(Exception ex)
                { 
                }
            }
        }
        public List<LoginNewSave> getLoginNewSaveData(string from, string to)
        {
            List<LoginNewSave> data = new List<LoginNewSave>();
            using (SqlConnection conn = ConnectionHelper.GetConnection())
            {
                SqlCommand command = new SqlCommand("sp_Select_LoginNewSave", conn);
                command.CommandTimeout = 3600;
                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.AddWithValue("@fromDT", from);
                command.Parameters.AddWithValue("@toDT", to);
                SqlDataReader dataReader = command.ExecuteReader();
                while (dataReader.Read())
                {
                    LoginNewSave pl = new LoginNewSave();
                    pl.Date = dataReader.GetDateTime(0).ToString("dd/MM/yyyy");
                    pl.Ticks = dataReader.GetDateTime(0).Ticks;
                    pl.Nau = dataReader.GetString(1);
                    pl.DaySecondSave = dataReader.GetString(2);
                    pl.DayThirdSave = dataReader.GetString(3);
                    pl.DaySevenSave = dataReader.GetString(4);
                    pl.DayFourteenSave = dataReader.GetString(5);
                    pl.DayThirtySave = dataReader.GetString(6);
                    pl.PercentDaySecondSave = dataReader.GetString(7);
                    pl.PercentDayThirdSave = dataReader.GetString(8);
                    pl.PercentDaySevenSave = dataReader.GetString(9);
                    pl.PercentDayFourteenSave = dataReader.GetString(10);
                    pl.PercentDayThirtySave = dataReader.GetString(11);
                    data.Add(pl);
                }
            }
            return data;
        }
    }
}
